package com.sia.springcloud.tool;



import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class DBConnectSQL {

	private Connection conn = null;
	public Statement stmt = null;
	private ResultSet rs = null;
	private PreparedStatement ps = null;
	private static String driver = "com.microsoft.sqlserver.jdbc.SQLServerDriver";
	//private final static String url = "jdbc:sqlserver://20.62.104.16:1433;databasename=";
	private String url = "jdbc:sqlserver://127.0.0.1:1433;databasename=";
	private String db = "BOS_6000_DB";
	//private final static String db = "BOS_SQLSERV_2005";
	//private final static String db = "BOS_SQLSERV_2005";
	private String user = "ExtUser";
	private String pwd = "bos6000";
	private static Logger logger = LoggerFactory.getLogger(DBConnectSQL.class);
	public DBConnectSQL(String serverurl, Integer serverport, String dbname, String dbuser, String dbpwd) {
		this.url = "jdbc:sqlserver://"+serverurl+":"+serverport+";databasename=";
		this.db = dbname;
		this.user = dbuser;
		this.pwd = dbpwd;
		try {
			Class.forName(driver);
			conn = DriverManager.getConnection(url+db,user,pwd);
			//conn = DriverManager.getConnection(url+db+"?testOnBorrow=true&;validationQuery=select 1 ",user,pwd);
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
		} catch (SQLException ex) {
			logger.info(ex.toString() + "111·aa");
		} catch (ClassNotFoundException ex) {
			logger.info(ex.toString() + "222bb");
		}
	}

	public PreparedStatement getPs(String sql) throws SQLException {
		try {
			ps = conn.prepareStatement(sql);
			conn.commit();
			return ps;
		} catch (Exception e) {
			// conn.rollback();
			e.printStackTrace();
			return ps;
		}
	}

	public ResultSet executeQuery(String ssql) throws SQLException {
		try {
			rs = stmt.executeQuery(ssql);
			return rs;

		} catch (SQLException se) {
			// conn.rollback();
			System.out
					.println("DBBean.executeQuery() ERROR:" + se.getMessage());
		}
		return rs;
	}

	public int executeUpdate(String ssql) throws SQLException {
		int iupdate = 0;
		try {
			iupdate = stmt.executeUpdate(ssql);
			return iupdate;
		} catch (SQLException se) {
			// conn.rollback();
			System.out.println("DBBean.executeUpdate() ERROR:"
					+ se.getMessage());
		}
		return iupdate;
	}

	public void free(){
		try {
			if (rs != null)
				rs.close();
			if (stmt != null)
				stmt.close();
			if (conn != null)
				conn.close();
		} catch (SQLException se) {
			System.out.println("DBBean.free() ERROR:" + se.getMessage());
		}
	}
    public static void createTable(String name){
		DBConnectSQL dbc=new DBConnectSQL("192.168.1.13",1433, "db", "sa","sa");
		String create="CREATE TABLE `"+name+"` (\n" +
				"  `id` int(11) NOT NULL AUTO_INCREMENT,\n" +
				"  `variable` int(11) DEFAULT NULL,\n" +
				"  `value` double DEFAULT NULL,\n" +
				"  `time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,\n" +
				"  PRIMARY KEY (`id`)\n" +
				") ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;";
		try{
			dbc.executeUpdate(create);
			dbc.free();
		}catch (SQLException e){
		}
	}
	public static void dropTable(String name){
		DBConnectSQL dbc=new DBConnectSQL("192.168.1.13",1433, "db", "sa","sa");
		String create="drop table "+name;
		try{
			dbc.executeUpdate(create);
			dbc.free();
		}catch (SQLException e){
		}
	}
	public static void insertvalue(String table,String variable,String value){
		DBConnectSQL dbc=new DBConnectSQL("192.168.1.13",1433, "db", "sa","sa");
		String create="insert into "+table+" values("
				+null+","
				+variable+","
				+value+","
				+"now())";
		try{
			System.out.println(create);
			dbc.executeUpdate(create);
			dbc.free();
		}catch (SQLException e){
		}
	}
	public Connection getConnection() {
		return conn;
	}
public static void testDB(){
	String create1="CREATE TABLE  if not exists `offset` (\n" +
			"  `id` int(11) NOT NULL AUTO_INCREMENT,\n" +
			"  `offset` int(11) DEFAULT NULL,\n" +
			"  `flag` int(11) DEFAULT NULL,\n" +
			"  PRIMARY KEY (`id`)\n" +
			") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
	String create2="CREATE TABLE  if not exists `offset2` (\n" +
			"  `id` int(11) NOT NULL AUTO_INCREMENT,\n" +
			"  `offset` int(11) DEFAULT NULL,\n" +
			"  `flag` int(11) DEFAULT NULL,\n" +
			"  PRIMARY KEY (`id`)\n" +
			") ENGINE=InnoDB DEFAULT CHARSET=utf8;";
	String query1="select * from offset";
	String query2="select * from offset2";
	String insert1="insert into offset values(1,23,-1)";
	String insert2="insert into offset2 values(1,23,-1)";
	logger.info("开始连接数据库");
	DBConnectSQL dbConnectSQL =new DBConnectSQL("192.168.1.13",1433, "db", "sa","sa");
	logger.info("数据库连接完成");
	try{
		logger.info("建表语句开始执行");
		dbConnectSQL.executeUpdate(create1);
		dbConnectSQL.executeUpdate(create2);
		logger.info("建表语句执行完成");
		logger.info("插入语句开始执行");
		ResultSet resultSet1= dbConnectSQL.executeQuery(query1);
		if(!resultSet1.next()){
			dbConnectSQL.executeUpdate(insert1);
		}
		ResultSet resultSet2= dbConnectSQL.executeQuery(query2);
		if(!resultSet2.next()){
			dbConnectSQL.executeUpdate(insert2);
		}
		logger.info("插入语句执行完成");
		logger.info("查询语句开始执行");
		ResultSet resultSet3= dbConnectSQL.executeQuery(query1);
		if(resultSet3.next()){
			logger.info("查询值为"+resultSet3.getString("offset"));
		}
		logger.info("查询语句执行完成");
	}catch(SQLException e){
		logger.info(e.getMessage() + "333bb");
	}
}
	public static List<String> readFile02(String path) throws IOException {
		// 使用一个字符串集合来存储文本中的路径 ，也可用String []数组
		List<String> list = new ArrayList<String>();
		FileInputStream fis = new FileInputStream(path);
		// 防止路径乱码  如果utf-8 乱码 改GBK   eclipse里创建的txt 用UTF-8，在电脑上自己创建的txt 用GBK
		InputStreamReader isr = new InputStreamReader(fis, "UTF-8");
		BufferedReader br = new BufferedReader(isr);
		String line = "";
		while ((line = br.readLine()) != null) {
			// 如果 t x t文件里的路径 不包含---字符串    这里是对里面的内容进行一个筛选
			if (line.lastIndexOf("---") < 0) {
				list.add(line);
			}
		}
		br.close();
		isr.close();
		fis.close();
		return list;
	}
//	public static void main(String args[]){
//		try{
//			System.out.println("haha");
//			DBConnectSQL dbc=new DBConnectSQL();
//			String[]tables={
////					"Ext_CommunicationState_V",
////					"ExtActualErrorQueue_V",
////					"ExtActualValuesSG_V",
//					"ExtDataChangeProt_RDS_V",
//					"ExtDataChangeProt_V",
////					"ExtendedWeldSpotsWithCommentsBlockLength_V",
////					"ExtError_RDS_V",
//					"ExtError_V",
////					"ExtGlobalOptions_V",
////					"ExtParamValues_NWI_V",
////					"ExtParamValues_NWISpName_V",
////					"ExtParamValues_RDS_V",
////					"ExtParamValues_V",
////					"ExtSpotTable_V",
////					"ExtSpotTableVirtual_V",
//					"ExtWeldFaultProt_V",
////					"ExtWeldMeasureProt_NWI_V",
////					"ExtWeldMeasureProt_RDS_V",
////					"ExtWeldMeasureProt_RDS2_V",
//					//"ExtWeldMeasureProt_V"
////					"ExtWeldMeasureProt2_V"
////					"ExtWeldMeasureProt3_V",
//					//"ExtWeldMeasureProtDDW_V"
//			};
//			for(int i=0;i<tables.length;i++){
//				String projectPath=System.getProperty("user.dir") + "/project/"+tables[i]+".xlsx";
//				String sql="select * from "+tables[i]
//						+" where timerName='WGTS_J1_Timer'";
//				System.out.println(sql);
//				ResultSet resultSet=dbc.executeQuery(sql);
//				ResultSetMetaData meta = resultSet.getMetaData();
//				int columeCount = meta.getColumnCount();
//				ArrayList title=new ArrayList();
//				for (int j = 1; j < columeCount + 1; j++) {
//					String tt=meta.getColumnName(j);
//					title.add(tt);
//				}
//				ArrayList arrayList=new ArrayList();
//				int size=0;
//				while(resultSet.next()){
//					HashMap hashMap=new HashMap();
//					for (int j = 1; j < columeCount + 1; j++) {
//						String key=meta.getColumnName(j);
//						String value=resultSet.getString(key);
//						hashMap.put(key,value);
//					}
//					arrayList.add(hashMap);
//					size++;
//				}
//				POIUtil.writeExcel(projectPath,"Sheet1",title,arrayList);
//			}
//		}catch(Exception e){}
//
//	}

}
